<?php
/**
 * @author Marat Komarov <bassguitarrer@gmail.com>
 */

class Cruiser_NSTree_Table extends Zend_Db_Table
{
    const LEFT         = 'left';
    const RIGHT        = 'right';
    const LEVEL        = 'level';
    const DATA_FOREIGN = 'dataForeign';
    
    /**
     * @var string
     */
    const CONDITION    = 'condition';
    
    /**
     * @var string
     */
    const CONDITION_2    = 'condition2';

    protected $_level;

    protected $_left;

    protected $_right;

    protected $_dataForeign;
    
    /**
     * Condition list for table2
     * 
     * @var array
     */
    public $_condition;
    
    /**
     * Condition list for table2
     * 
     * @var array
     */
    public $_condition2;

    public function __construct($config)
    {
        if (isset($config[self::LEFT])) {
            $this->_left = $config[self::LEFT];
        }
        if (isset($config[self::RIGHT])) {
            $this->_right = $config[self::RIGHT];
        }
        if (isset($config[self::LEVEL])) {
            $this->_level = $config[self::LEVEL];
        }
        if (isset($config[self::DATA_FOREIGN])) {
            $this->_dataForeign	= $config[self::DATA_FOREIGN];
        }
        if (isset($config[self::CONDITION])) {
            $this->_condition	= $config[self::CONDITION];
        }
        if (isset($config[self::CONDITION_2])) {
            $this->_condition2	= $config[self::CONDITION_2];
        }

        parent::__construct($config);
    }
    
    /**
     * Set condtition to Select object
     * 
     * @param Zend_Db_Select $select    Select object
     * @param int $axis
     * @param string $tableName         Table name which used in condition
     * @param string $tableName2        Table name which used in condition
     * @param int $onlyFor              1 or 2. Condition used on for # table
     * @return void
     */
    function setCondition($select, $axis, $tableName = null, $tableName2 = null, 
    $condition = null, $onlyFor = null)
    {
        $condition = ($condition ? $condition : $this->_condition);
        if ($condition) {
            $tableName = ($tableName ? "`" . $tableName . "`." : '');
            $tableName2 = ($tableName2 ? "`" . $tableName2 . "`." : '');
            if (! is_array($condition)) {
                $condition = array($condition);
            }
            foreach ($condition as $col => $val) {
                if (is_int($col)) {
                    $col = $val;
                    $val = null;
                } else {
                    $col = "`" . $col . "` = ?";
                }
                if ($onlyFor == null || $onlyFor != null && $onlyFor != 1) {
                    $select->where($tableName . $col, $val);
                }
                if (($onlyFor == null || $onlyFor != null && $onlyFor != 1) 
                && $axis != Cruiser_NSTree::AXIS_SELF) {
                    $select->where($tableName2 . $col, $val);
                }
            }
        }
    }
    
    /**
     * Get SQL condition
     * 
     * @param string $tableName Table name which used in condition
     * @param bool $isTable2    If true then use condition for table2
     * @return string           SQL condition
     */
    function getConditionSql($tableName = null, $isTable2 = false)
    {
        $where = '';
        $whereArr = ($isTable2 ? $this->_condition2 : $this->_condition );
        if ($whereArr) {
            $tableName = ($tableName ? "`" . $tableName . "`." : '');
            foreach ($whereArr as $key => $val) {
                $where .= " AND " . $tableName . "`" . $key . "` = '" . $val . "' ";
            }
        }
        return $where;
    }
    
    /**
     * Get condition
     * 
     * @param string $tableName Table name which used in condition
     * @param bool $isTable2    If true then use condition for table2
     * @param bool $original    If true then use condition without name changing
     * @return array            Array with conditions
     */
    function getCondition($tableName = null, $isTable2 = false, $original = false)
    {
        $where = '';
        $whereArr = ($isTable2 ? $this->_condition2 : $this->_condition );
        if ($original) {
            return $whereArr;
        }
        $whereReturn = array();
        if ($whereArr) {
            $tableName = ($tableName ? "`" . $tableName . "`." : '');
            foreach ($whereReturn as $col => $val) {
                $whereReturn[$tableName . "`" . $col . "` = ?"] = $val;
            }
        }
        return $whereReturn;
    }


    /**
	 * Выборка системных полей узла.
	 *
	 * Системные это primary, left, right, level, dataForeign?
	 *
	 * @param int $id
	 * @param int $axis
	 * @param array $params
	 * @return Zend_Db_Select
	 */
    public function makeSelect($id, $axis, $params = array())
    {
        if (isset($params['select']) && $params['select'] instanceof Zend_Db_Select) {
            $select = $params['select'];
            $isParamSelect = true;
        } else {
            $select = $this->_db->select();
            $isParamSelect = false;
        }
        
        $info = $this->info();
        $cols = array();

        $primary = (array) $this->_primary;
        $pkIdentity = $primary[(int)$this->_identity];

        foreach (array_values($info['cols']) as $column) {
            $cols[] = new Zend_Db_Expr("s1.$column AS $column");
        }
        
        if ($isParamSelect) {
            $select->columns($cols);
        } else {
            $select->from(array('s1' => $this->_name), $cols);
        }
        
        if ($axis != Cruiser_NSTree::AXIS_SELF) {
            $select->from(array('s2' => $this->_name), array());
        }
        
        $this->setCondition($select, $axis, 's1', 's2');

        if ($id && is_array($id)) {
            $indentCond = 's%d.%s IN (?)';
        } else {
            $indentCond = 's%d.%s = ?';
        }
        
        $indentValue = $id ? $id : '1';
        $indentCond = sprintf($indentCond,
            $axis == Cruiser_NSTree::AXIS_SELF ? '1' : '2',
            $id ? $pkIdentity : $this->_left
        );
        $select->where($indentCond, $indentValue);


        switch ($axis) {
            case Cruiser_NSTree::AXIS_CHILD:
            case Cruiser_NSTree::AXIS_CHILD_OR_SELF:
            case Cruiser_NSTree::AXIS_LEAF:
            case Cruiser_NSTree::AXIS_DESCENDANT:
            case Cruiser_NSTree::AXIS_DESCENDANT_OR_SELF:

                if ($axis == Cruiser_NSTree::AXIS_CHILD) {
                    $select->where("s1.{$this->_level} = s2.{$this->_level} + 1");
                }

                if ($axis == Cruiser_NSTree::AXIS_CHILD_OR_SELF) {
                    $select->where("s1.{$this->_level} - s2.{$this->_level} <= ?", "1");
                }

                if ($axis == Cruiser_NSTree::AXIS_LEAF) {
                    $select->where("s1.{$this->_left} = s1.{$this->_right} - 1");
                }

                if ($axis == Cruiser_NSTree::AXIS_DESCENDANT_OR_SELF ||
                $axis == Cruiser_NSTree::AXIS_CHILD_OR_SELF) {
                    $select->where("(s1.{$this->_left} BETWEEN s2.{$this->_left} AND s2.{$this->_right})");
                }
                else {
                    $select->where("s1.{$this->_left} > s2.{$this->_left} AND s1.{$this->_right} < s2.{$this->_right}");
                }

                break;

            case Cruiser_NSTree::AXIS_PARENT:
                $select->where("s1.{$this->_level} = s2.{$this->_level} - 1");
                // брейка нет

            case Cruiser_NSTree::AXIS_ANCESTOR:
            case Cruiser_NSTree::AXIS_ANCESTOR_OR_SELF:
                if ($axis == Cruiser_NSTree::AXIS_ANCESTOR_OR_SELF) {
                    $select->where("s1.{$this->_left} <= s2.{$this->_left}");
                    $select->where("s1.{$this->_right} >= s2.{$this->_right}");
                }
                else {
                    $select->where("s1.{$this->_left} < s2.{$this->_left}");
                    $select->where("s1.{$this->_right} > s2.{$this->_right}");
                }

                break;

            case Cruiser_NSTree::AXIS_FOLLOWING_SIBLING:
            case Cruiser_NSTree::AXIS_PRECEDING_SIBLING:
                if (($info = $this->getParentInfo($id)) != false) {
                    $select->where("s2.{$this->_level} = s1.{$this->_level}");
                    $select->where("s1.{$this->_left} > ?", $info[$this->_left]);
                    $select->where("s1.{$this->_right} < ?", $info[$this->_right]);

                    if ($axis == Cruiser_NSTree::AXIS_FOLLOWING_SIBLING) {
                        $select->where("s1.{$this->_left} > s2.{$this->_right}");
                    }
                    else {
                        $select->where("s1.{$this->_right} < s2.{$this->_left}");
                    }
                }
                else {
                    throw new Cruiser_NSTree_Exception('Parent node was not found');
                    return;
                }
                break;

        }

        // Process additional parameters
        if (isset($params["depth"]) &&
            in_array( $axis, array(Cruiser_NSTree::AXIS_ANCESTOR,
            Cruiser_NSTree::AXIS_ANCESTOR_OR_SELF,
            Cruiser_NSTree::AXIS_DESCENDANT,
            Cruiser_NSTree::AXIS_DESCENDANT_OR_SELF))
        )
        {

            $depth = abs($params["depth"]);

            if (in_array ($axis, array(Cruiser_NSTree::AXIS_DESCENDANT,
                Cruiser_NSTree::AXIS_DESCENDANT_OR_SELF))) {

                $select->where("s1.{$this->_level} <= s2.{$this->_level} + ?", $depth);
            }
            else {
                $select->where("s1.{$this->_level} >= s2.{$this->_level} - ?", $depth);
            }
        } elseif (isset($params['slice1']) && $params['slice2']) {
            $slice1 = abs($params["slice1"]);
            $slice2 = abs($params["slice2"]);

            if ($slice2 < $slice1) {
                throw new Cruiser_NSTree_Exception('Parameter slice2 must be more slice1');
            }
            else {
                $ax_desc = in_array ($axis, array(Cruiser_NStree::AXIS_DESCENDANT,
                    Cruiser_NSTree::AXIS_DESCENDANT_OR_SELF));
                $select->where("( "
                    . "s1.{$this->_level} BETWEEN "
                    . "s2.{$this->_level} " . ($ax_desc ? '+'.$slice1 : '-'.$slice2) . " AND "
                    . "s2.{$this->_level} " . ($ax_desc ? '+'.$slice2 : '-'.$slice1)
                    . " )"
                );
            }
        }

        if (! empty($params['where'])) {
            $this->setCondition($select, $axis, 's1', null, $params['where'], 1);
        }

        if (empty($params['order'])) {
            $select->order(new Zend_Db_Expr("s1.{$this->_left}"));
        } elseif ($params['order'] == 'left') {
            $select->order(new Zend_Db_Expr("s1.{$this->_left}"));
        } elseif ($params['order'] == 'right') {
            $select->order(new Zend_Db_Expr("s1." . $this->_right . " DESC"));
        } else {
            if (is_array($params['order'])) {
                foreach ($params['order'] as $val) {
                    $select->order(new Zend_Db_Expr("s1." . $val));
                }
            } else {
                $select->order(new Zend_Db_Expr("s1." . (string) $params['order']));
            }
        }
        
        if (! empty($params['skipRoot'])) {
            $select->where('`s1`.' . $this->_level . ' > 0');
        }
        
        return $select;
    }

    /**
	 * @param int $id
	 * @return array
	 */
    public function getNodeInfo($id)
    {
        $select = $this->makeSelect($id, Cruiser_NSTree::AXIS_SELF);
        return $this->_db->fetchRow($select->__toString(), null, Zend_Db::FETCH_ASSOC);
    }

    /**
	 * @param int $id
	 * @return array
	 */
    public function getParentInfo($id)
    {
        $select = $this->makeSelect($id, Cruiser_NSTree::AXIS_PARENT);
        return $this->_db->fetchRow($select->__toString());
    }

    /**
	 * Резервирует место в дереве для дочернего узла
	 *
	 * @param int $id
	 * @return int Уникальный ключ нового узла
	 */
    public function allocChild($id, $pos, $data = null)
    {
        $p = $this->getNodeInfo($id);
        if (! $p) {
            throw new Cruiser_NSTree_Exception('Parent node not found');
        }
        if ($pos == Cruiser_NSTree::AT_BEGIN) {
            $v = $p[$this->_left];
            $left = $p[$this->_left] + 1;
            $right = $p[$this->_left] + 2;
        } elseif ($pos == Cruiser_NSTree::AT_END) {
            $v = $p[$this->_right];
            $left = $p[$this->_right];
            $right = $p[$this->_right] + 1;
        } else {
            throw new Cruiser_NSTree_Exception("invalid node position '$pos'");
        }

        $sql = "
            UPDATE {$this->_name}
            SET
                left  =
                    CASE
                        WHEN left > :v THEN left + 2
                        ELSE left
                    END,
                right =
                    CASE
                        WHEN right >= :v THEN right + 2
                        ELSE right
                    END
                WHERE
                right >= :v
                " . $this->getConditionSql() . "
        ";
        $sql = $this->_inflectQuery($sql);
        $this->_db->query($sql, array("v" => $v));

        if ($data && is_array($data)) {
            $columns = $data;
        } else {
            $columns = array();
        }
        $columns[$this->_left]  = $left;
        $columns[$this->_right] = $right;
        $columns[$this->_level] = $p[$this->_level] + 1;
        return $this->insert($columns);
    }

    /**
	 * Резервирует место в дереве для братского узла
	 *
	 * @param int $id
	 * @param int $pos Cruiser_NSTree::BEFORE, Cruiser_NSTree::AFTER
	 * @return int
	 */
    function allocSibling($id, $pos)
    {
        $n = $this->getNodeInfo($id);

        if ($pos == Cruiser_NSTree::BEFORE) {
            $v = $n[$this->_left];
            $left = $n[$this->_left];
            $right = $n[$this->_left] + 1;
        }
        elseif ($pos == Cruiser_NSTree::AFTER) {
            $v = $n[$this->_right];
            $left = $n[$this->_right] + 1;
            $right = $n[$this->_right] + 2;
        }
        else {
            throw new Cruiser_NSTree_Exception("invalid node position '$pos'");
        }

        $sql = "
 			UPDATE {$this->_name}
		    SET
		    	left =
		    		CASE
		    		WHEN left >= :v THEN
		    			left + 2
		    		ELSE
		    			left
		    		END,
		    	right =
		    		CASE
	    			WHEN right >= :v THEN
	    				right + 2
	    			ELSE
	    				right
		    		END
		    WHERE
		    	right > :v
		    	" . $this->getConditionSql() . "
		";
        $sql = $this->_inflectQuery($sql);
        $this->_db->query($sql, array('v' => $v));

        $columns = array(
            $this->_left	=> $left,
            $this->_right	=> $right,
            $this->_level	=> $n[$this->_level]
        );
        return $this->insert($columns);

    }

    /**
	 * Перемещение узла к новому родителю
	 *
	 * @param int $id
	 * @param int $newParentId
	 * @return bool true при удачной операции
	 * @throws Cruiser_NSTree_Exception
	 */
    public function replaceNode($id, $newParentId)
    {
        $primary = (array) $this->_primary;
        $pkIdentity = $primary[(int)$this->_identity];

        $n = $this->getNodeInfo($id);
        $p = $this->getNodeInfo($newParentId);

        if ($n[$pkIdentity] == $p[$pkIdentity]) {
            throw new Cruiser_NSTree_Exception('node can\'t be replaced to itself');
        }

        $holders = array(
            'L_n' => $n[$this->_left],
            'R_n' => $n[$this->_right],
            'V_n' => $n[$this->_level],
            'L_p' => $p[$this->_left],
            'R_p' => $p[$this->_right],
            'V_p' => $p[$this->_level]
        );

        if ($p[$this->_left] < $n[$this->_left] &&
        $p[$this->_right] > $n[$this->_right]) {
            // intersection
            $sql = "
                UPDATE {$this->_name}
                SET
                    level =
                        CASE
                        WHEN left BETWEEN :L_n AND :R_n THEN
                            level - (:V_n - :V_p - 1)
                        ELSE
                            level
                        END,
                    right =
                        CASE
                        WHEN right BETWEEN :R_n+1 AND :R_p-1 THEN
                            right - (:R_n -:L_n + 1)
                        ELSE
                            CASE
                            WHEN left BETWEEN :L_n AND :R_n THEN
                                right + (:R_p - :R_n - 1)
                            ELSE
                                right
                            END
                        END,
                    left =
                        CASE
                        WHEN left BETWEEN :R_n+1 AND :R_p-1 THEN
                            left - (:R_n - :L_n + 1)
                        ELSE
                            CASE
                            WHEN left BETWEEN :L_n AND :R_n THEN
                               left + (:R_p - :R_n - 1)
                            ELSE
                                left
                            END
                        END
                WHERE
                    left BETWEEN :L_p+1 AND :R_p-1
                    " . $this->getConditionSql() . "
            ";
        }
        elseif ($n[$this->_left] > $p[$this->_right]) {
            // after
            $sql =  "
                UPDATE {$this->_name}
                SET
                	level =
                		CASE
                		WHEN left BETWEEN :L_n AND :R_n THEN
                			level - (:V_n - :V_p - 1)
                		ELSE
                			level
                		END,
                    left =
                    	CASE
                    	WHEN left BETWEEN :R_p AND (:L_n-1) THEN
                    		left + (:R_n-:L_n+1)
                    	ELSE
                    		CASE
                    		WHEN left BETWEEN :L_n AND :R_n THEN
                    			left-(:L_n-:R_p)
                    		ELSE
                            	left
                            END
						END,
                    right =
                    	CASE
                    	WHEN right BETWEEN :R_p AND :L_n THEN
                        	right + (:R_n - :L_n +1)
                        ELSE
                        	CASE
                        	WHEN right BETWEEN :L_n AND :R_n THEN
	                            right-(:L_n-:R_p)
							ELSE
                            	right
                            END
						END
                WHERE
                    (
                	left BETWEEN :L_p AND :R_n
                		OR
                	right BETWEEN :L_p AND :R_n
                	) " . $this->getConditionSql() . "
            ";
        }
        elseif ($n[$this->_right] < $p[$this->_left]) {
            // before
            $sql = "
                UPDATE {$this->_name}
                SET
                    level =
                		CASE
                		WHEN left BETWEEN :L_n AND :R_n THEN
                			level - (:V_n - :V_p - 1)
                		ELSE
                			level
                		END,
                    left =
                    	CASE
                    	WHEN left BETWEEN :R_n AND :R_p THEN
                        	left - (:R_n - :L_n + 1)
                        ELSE
                        	CASE
                        	WHEN left BETWEEN :L_n AND :R_n THEN
                            	left+(:R_p-1-:R_n)
                            ELSE
                            	left
                            END
                        END,
                    right =
                    	CASE
                    	WHEN right BETWEEN (:R_n+1) AND (:R_p-1) THEN
                        	right-(:R_n-:L_n+1)
                        ELSE
                        	CASE
                        	WHEN right BETWEEN :L_n AND :R_n THEN
                            	right+(:R_p-1-:R_n)
                            ELSE
                            	right
                            END
                        END
                WHERE
                    (
                    left BETWEEN :L_n AND :R_p
                    	OR
                    right BETWEEN :L_n AND :R_p
                    ) " . $this->getConditionSql() . "
            ";
        }
        else {
            throw new Cruiser_NSTree_Exception('node can\'t be placed here');
        }

        $sql = $this->_inflectQuery($sql);
        $this->_db->query($sql, $holders);

    }

    /**
	 * Переместить узел перед указанным
	 *
	 * @param int $id
	 * @param int $beforeId
	 * @return bool
	 * @throws Cruiser_NSTree_Exception
	 */
    public function replaceBefore($id, $beforeId)
    {
        $n = $this->getNodeInfo($id);
        $b = $this->getNodeInfo($beforeId);

        if ($b[$this->_left] == 1) {
            throw new Cruiser_NSTree_Exception('node can\'t be placed before root');
        }

        $placeholders = array(
            'L_n' => $n[$this->_left],
            'R_n' => $n[$this->_right],
            'V_n' => $n[$this->_level],
            'L_b' => $b[$this->_left],
            'R_b' => $b[$this->_right],
            'V_b' => $b[$this->_level]
        );

        if ($n[$this->_left] > $b[$this->_left] &&
        $n[$this->_right] < $b[$this->_right]) {
            // intersection
            $sql = "
				UPDATE $this->_name
				SET
					level =
						CASE
						WHEN left BETWEEN :L_n AND :R_n THEN
							level - (:V_n - :V_b)
						ELSE
							level
						END,
				    right =
						CASE
						WHEN left BETWEEN :L_n AND :R_n THEN
							right - (:L_n - :L_b)
						WHEN right BETWEEN :L_b AND :L_n-1 THEN
							right + (:R_n - :L_n + 1)
						ELSE
							right
						END,
				    left =
						CASE
						WHEN left BETWEEN :L_n AND :R_n THEN
							left - (:L_n - :L_b)
						WHEN left BETWEEN :L_b AND :L_n-1 THEN
							left + (:R_n - :L_n + 1)
						ELSE
							left
						END
				WHERE
					left BETWEEN :L_b AND :R_b
					" . $this->getConditionSql() . "
			";
        }
        elseif ($n[$this->_right] < $b[$this->_left]) {
            // before
            $sql = "
				UPDATE $this->_name
				SET
					level =
						CASE
						WHEN left BETWEEN :L_n AND :R_n THEN
							level - (:V_n - :V_b)
						ELSE
							level
						END,
				    right =
						CASE
						WHEN left BETWEEN :L_n AND :R_n THEN
							right + (:L_b - :R_n - 1)
						WHEN right BETWEEN :R_n+1 AND :L_b-1 THEN
							right - (:R_n - :L_n + 1)
						ELSE
							right
						END,
				    left =
						CASE
						WHEN left BETWEEN :L_n AND :R_n THEN
							left + (:L_b - :R_n - 1)
						WHEN left BETWEEN :R_n+1 AND :L_b-1 THEN
							left - (:R_n - :L_n + 1)
						ELSE
							left
						END
				WHERE
					left BETWEEN :L_n AND :L_b-1
						OR
					right BETWEEN :L_n AND :L_b-1
			";
        }
        elseif ($n[$this->_left] > $b[$this->_right]) {
            // after
            $sql = "
				UPDATE $this->_name
				SET
					level =
						CASE
						WHEN left BETWEEN :L_n AND :R_n THEN
							level - (:V_n - :V_b)
						ELSE
							level
						END,
					right =
						CASE
						WHEN left BETWEEN :L_n AND :R_n THEN
							right - (:L_n - :L_b)
						WHEN right BETWEEN :L_b AND :L_n-1 THEN
							right + (:R_n - :L_n + 1)
						ELSE
							right
						END,
				    left =
						CASE
						WHEN left BETWEEN :L_n AND :R_n THEN
							left - (:L_n - :L_b)
						WHEN left BETWEEN :L_b AND :L_n-1 THEN
							left + (:R_n - :L_n + 1)
						ELSE
							left
						END
				WHERE
				    {
					left BETWEEN :L_b AND :R_n
						OR
					right BETWEEN :L_b AND :R_n
					} " . $this->getConditionSql() . "
			";
        }
        else {
            throw new Cruiser_NSTree_Exception('node can\'t be placed here');
        }

        $sql = $this->_inflectQuery($sql);
        $this->_db->query($sql, $placeholders);
    }

    /**
	 * Удаление узла
	 *
	 * @param int $id
	 * @param bool $removeChilds
	 * @return bool
	 */
    function deleteNode($id, $removeChilds)
    {
        $primary = (array) $this->_primary;
        $pkIdentity = $primary[(int)$this->_identity];

        $n = $this->getNodeInfo($id);

        $holders = array(
            'L' => $n[$this->_left],
            'R' => $n[$this->_right]
        );

        if ($removeChilds) {
            // delete node and his children
            $sql = "DELETE FROM $this->_name "
            . "WHERE $this->_left BETWEEN :L AND :R";
            $this->_db->query($sql, $holders);

            // clear blank spaces in a tree
            $holders['D'] = $n[$this->_right] - $n[$this->_left] + 1;
            $sql = "
                UPDATE {$this->_name}
                SET
                    left =
                    	CASE
                    	WHEN left > :L THEN
                    		left - :D
                    	ELSE
                    		left
                    	END,
                    right =
                    	CASE
                    	WHEN right > :L THEN
                    		right - :D
                    	ELSE
                    		right
                    	END
                WHERE
                	right > :R
                	" . $this->getConditionSql() . "
            ";
            $sql = $this->_inflectQuery($sql);
            return $this->_db->query($sql, $holders);
        }
        else {
            // delete node structure
            $where = $this->_db->quoteInto("{$pkIdentity} = ?", $n[$pkIdentity]);
            $this->delete($where);

            // replace children to node parent
            $sql = "
		        UPDATE {$this->_name}
		        SET
		            level =
		            	CASE
		            	WHEN left BETWEEN :L AND :R THEN
		            		level-1
		            	ELSE
		            		level
		            	END,
		          left =
		            	CASE
		            	WHEN left BETWEEN :L AND :R THEN
		            		left - 1
		            	WHEN left > :R THEN
		            		left - 2
		            	ELSE
		            		left
		            	END,
		            right =
		            	CASE
		            	WHEN right BETWEEN :L AND :R-1 THEN
		            		right - 1
		            	WHEN right >= :R THEN
		            		right - 2
		            	ELSE
		            		right
		            	END
		        WHERE right > :L
		        " . $this->getConditionSql() . "
			";
            $sql = $this->_inflectQuery($sql);
            return $this->_db->query($sql, $holders);
        }
    }

    protected function _inflectQuery($sql)
    {
        $regexp = '/[^\:](left|right|level|primary|dataForeign)/';
        return preg_replace_callback($regexp, array($this, '_inflectCallback'), $sql);

    }

    protected function _inflectCallback($matches)
    {
        $column = "_{$matches[1]}";
        return str_replace($matches[1], $this->$column, $matches[0]);
    }

}
